package yu.zhou.njupt.importdata.core.impl;

import java.io.DataInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStreamReader;
import java.nio.charset.Charset;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.apache.commons.dbutils.DbUtils;

import com.google.common.base.Joiner;

import au.com.bytecode.opencsv.CSVReader;
import yu.zhou.njupt.importdata.core.Importer;
import yu.zhou.njupt.importdata.entity.ImportResult;
import yu.zhou.njupt.importdata.entity.enumtype.CreateTable;
import yu.zhou.njupt.importdata.entity.enumtype.DoTruncate;
import yu.zhou.njupt.importdata.entity.enumtype.NewImport;
import yu.zhou.njupt.importdata.util.RegexUtil;

/** 
 * 用于导入csv文件(文件只有一个sheet)。一个文件代表一张表。根据文件
 * 名来确定导入方式，文件名形如'EPC_MODEL(XXXX).csv'的为普通表，
 * EPC_MODEL会被做为表名，括号是可选的；文件名
 * 形如'EPC_MODEL-20150313(XXXX).csv'的是分区表，EPC_MODEL会作为表名，'20150313'会作为分区键，
 * 'P20150313'会作为新增的分区名称。
**/
@SuppressWarnings("unused")
public class CsvImporter2 extends Importer{

    public CsvImporter2(Connection conn, DoTruncate doTruncate, NewImport newImport, CreateTable createTable, int titleNum) throws Exception {
        super(conn, doTruncate, newImport, createTable, titleNum);
    }

    public ImportResult importData(File sourceFile,
            List<String> disableConsStrSet, List<String> enableConsStrSet, 
            List<String> disableIndexesStrSet, List<String> enableIndexesStrSet){
        int count = 0;
        int recordCount = 0;
        long elapsedTime = 0;
        String csvRow[] = null;
        int columnNum = -1;
        int cellNum = -1;
        String filename = null;
        String tableName = null;
        String sheetName = null;
        String partKey = null;
        ImportResult importResult = null;
        Statement s = null;
        PreparedStatement ps = null;
        CSVReader csvReader = null;
        List<String> columnList = null;
        List<String> placeholderList = null;
        List<Integer> indexList = null;
        
        try {
            Date startTime = new Date();
            DataInputStream dis = new DataInputStream(new FileInputStream(sourceFile));
            csvReader = new CSVReader(new InputStreamReader(dis, Charset.forName("GBK")));
            filename = sourceFile.getName();
            String insertStr = null;
            
            if (!hasDealt(filename, sheetName)) {
                tableName = RegexUtil.getValidName(filename).toUpperCase();
                int index1 = filename.lastIndexOf('-');
                if (index1 != -1) {// 分区表
                    partKey = RegexUtil.getDateStr(filename);
                }
                
                System.out.println("  开始向表" + tableName + "导入数据...");
                doBeforeImport(tableName, disableConsStrSet, enableConsStrSet,
                        disableIndexesStrSet, enableIndexesStrSet);
                String[] csvTitle = csvReader.readNext();
                columnList = new ArrayList<String>();
                placeholderList = new ArrayList<String>();
                indexList = new ArrayList<Integer>();
                for (int i = 0; i < csvTitle.length; i++) {
                    String titleCellText = csvTitle[i];
                    
                    if (!titleCellText.startsWith("@")) {
                        String columnName = RegexUtil.getValidName(titleCellText);
                        if (columnName != null) {
                            columnList.add(columnName);
                            placeholderList.add("?");
                            indexList.add(i);
                        }
                    }
                }
                s = conn.createStatement();
                if (partKey != null) {
                    try {
                        /* 添加分区 (如果分区已存在，会报出异常，但是只要分区存在，后面的都可正常执行，这里直接将异常catch住)*/
                        s.execute("alter table " + tableName
                                + " add partition p" + partKey + " values('"
                                + partKey + "')");
                    } catch (Exception e) {
                    }
                    insertStr = "insert into " + tableName + " ("
                            + Joiner.on(',').join(columnList) + ",table_version) "
                            + "values (" + Joiner.on(',').join(placeholderList) + ",'"
                            + partKey + "')";
                } else {
                    insertStr = "insert into " + tableName + " ("
                            + Joiner.on(',').join(columnList) + ") "
                            + "values (" + Joiner.on(',').join(placeholderList) + ")";
                }
                
                System.out.println(insertStr);
                ps = conn.prepareStatement(insertStr);
                
                for(int i = 0; i < titleNum - 1; i++) {
                    csvReader.readNext();
                }
                for (csvRow = csvReader.readNext(); csvRow != null; csvRow = csvReader
                        .readNext()) {
                    count++;
//                    System.out.println("总行数:" + recordCount);
//                    System.out.println("行数:" + count);
//                    System.out.println("单元格数:" + csvRow.length);
//                    if (recordCount == 180000 && count == 6099) {
//                        System.out.println("186099:");
//                        for (int ks = 0; ks < 100; ks++) {
//                            System.out.println("  " + csvRow[ks]);
//                        }
//                    }
//                    if (recordCount >= 3330000) {
//                        System.out.println("行：" + count);
//                    }
                    int paramIndex = 1;
                    for(int i = 0; i < indexList.size(); i++){
                        columnNum = i;
                        cellNum = indexList.get(i);
                        ps.setString(paramIndex++, csvRow[cellNum]);
                    }
                    ps.addBatch();
                    if (count == 10000) {
                        ps.executeBatch();
                        ps.clearBatch();
                        recordCount += count;
                        count = 0;
                        if (recordCount % 1000000 == 0) {
                            System.out.println("已插入" + recordCount + "条记录.");
                        }
                    }
                }
                ps.executeBatch();
                recordCount += count;
                doAfterImport(tableName, filename, sheetName);
                System.out.println("导入完成.记录数：" + recordCount);
                Date endTime = new Date();
                elapsedTime = endTime.getTime() - startTime.getTime();
            }
            importResult = new ImportResult(true, recordCount, elapsedTime);
        } catch (Exception e) {
            System.out.println("行：" + (recordCount + count));
            for(int i = 0; i < indexList.size(); i++){
                System.out.println("  " + columnList.get(i) + "-" + csvRow[indexList.get(i)]);
            }
//            for (String string : csvRow) {
//                System.out.print(string + "$");
//            }
//            System.out.println("行结束");
            e.printStackTrace();
            importResult = new ImportResult(false);
        } finally {
            DbUtils.closeQuietly(ps);
            try {
                csvReader.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }

        return importResult;
    }

}